﻿------------------------------------SCHEMAT OEM --------------------------------------------------------------------------------
CREATE TABLE oem.email
(
  id serial NOT NULL,
  klient_id integer,
  email character varying(400),
  CONSTRAINT email_id_pk PRIMARY KEY (id),
  CONSTRAINT email_klient_id_fk FOREIGN KEY (klient_id) REFERENCES oem.klient (id)
);

CREATE TABLE oem.numer_oferty_ustawienia
(
  nazwa character varying(50) NOT NULL,
  wartosc integer
);

CREATE TABLE oem.sposob_wysylki_oferty
(
  id serial NOT NULL,
  nazwa character varying(200),
  CONSTRAINT sposob_wysylki_oferty_id_pk PRIMARY KEY (id)
);
INSERT INTO oem.sposob_wysylki_oferty (nazwa) VALUES ('email');
INSERT INTO oem.sposob_wysylki_oferty (nazwa) VALUES ('faks');
INSERT INTO oem.sposob_wysylki_oferty (nazwa) VALUES ('telefonicznie');
INSERT INTO oem.sposob_wysylki_oferty (nazwa) VALUES ('poczta tradycyjna');


CREATE TABLE oem.status_oferty
(
  id serial NOT NULL,
  nazwa character varying(20),
  CONSTRAINT status_oferty_id_pk PRIMARY KEY (id)
);

INSERT INTO oem.status_oferty (nazwa) VALUES ('nowa');
INSERT INTO oem.status_oferty (nazwa) VALUES ('wysłana');
INSERT INTO oem.status_oferty (nazwa) VALUES ('utworzono zlecenie');
INSERT INTO oem.status_oferty (nazwa) VALUES ('anulowana');





CREATE TABLE oem.oferta
(
  id serial NOT NULL,
  data_oferty timestamp without time zone,
  data_waznosci timestamp without time zone,
  pracownik_id integer,
  klient_id integer,
  status_oferty_id integer,
  potwierdzenie_id integer,
  sposob_wysylki_oferty_id integer,
  uwagi character varying(400),
  numer character varying(20),
  email_id integer,
  opis_zakonczenia character varying(400),
  zlecenie_id integer DEFAULT 0,
  czas_realizacji character varying(500),
  CONSTRAINT oferta_pk PRIMARY KEY (id),
  CONSTRAINT oferta_klient_id_fk FOREIGN KEY (klient_id) REFERENCES oem.klient (id),
  CONSTRAINT oferta_pracownik_id_fk FOREIGN KEY (pracownik_id) REFERENCES oem.pracownik (id),
  CONSTRAINT oferta_sposob_wysylki_id_fk FOREIGN KEY (sposob_wysylki_oferty_id) REFERENCES oem.sposob_wysylki_oferty (id),
  CONSTRAINT oferta_status_oferty_id_fk FOREIGN KEY (status_oferty_id) REFERENCES oem.status_oferty (id)
  );

CREATE TABLE oem.pozycje_oferty
(
  id serial NOT NULL,
  oferta_id integer,
  numer_pozycji integer,
  gatunek_id integer,
  wymiar character varying(10),
  tol_dolna character varying(10),
  tol_gorna character varying(10),
  wydluzenie character varying(10),
  wytrzymalosc_min character varying(10),
  ilosc character varying(10),
  jednostka_miary_id integer,
  uwagi character varying(400),
  cena double precision,
  rabat integer,
  wytrzymalosc_max character varying(10),
  pakowanie_id integer,
  cena_opakowania boolean,
  waluta_id integer,
  powierzony_material boolean,
  magazyn_id integer,
  swiadectwo boolean,
  CONSTRAINT pozycje_oferty_id PRIMARY KEY (id),
  CONSTRAINT pozycje_oferty_gatunek_id_fk FOREIGN KEY (gatunek_id) REFERENCES oem.gatunek (id),
  CONSTRAINT pozycje_oferty_jednostka_miary_id_fk FOREIGN KEY (jednostka_miary_id) REFERENCES oem.jednostka_miary (id),
  CONSTRAINT pozycje_oferty_magazyn_id_fk FOREIGN KEY (magazyn_id) REFERENCES oem.magazyn (id),
  CONSTRAINT pozycje_oferty_oferta_id_fk FOREIGN KEY (oferta_id) REFERENCES oem.oferta (id),
  CONSTRAINT pozycje_oferty_pakowanie_id_fk FOREIGN KEY (pakowanie_id) REFERENCES oem.pakowanie (id),
  CONSTRAINT pozycje_oferty_waluta_id_fk FOREIGN KEY (waluta_id) REFERENCES oem.waluta (id)
);

CREATE TABLE oem.wysylka_oferty
(
  id serial NOT NULL,
  oferta_id integer,
  data_wysylki timestamp without time zone,
  pracownik_id integer,
  plik_oferty bytea,
  CONSTRAINT wysylka_oferty_id_pk PRIMARY KEY (id),
  CONSTRAINT wysylka_oferty_oferta_id_fk FOREIGN KEY (oferta_id) REFERENCES oem.oferta (id),
  CONSTRAINT wysylka_oferty_pracownik_id_fk FOREIGN KEY (pracownik_id) REFERENCES oem.pracownik (id)
);

ALTER TABLE oem.zlecenie
ADD oferta_id integer;


----------------------------------SCHEMAT queries_oem----------------------------------------------

UPDATE queries_oem.cls_zlecenie 
SET query = ('INSERT INTO oem.zlecenie(numer,data_utworzenia, data_zamowienia, termin, pracownik_id, klient_id, status_id, uwagi, sposob_zamowienia_id, tracking, adres_wysylki_id, numer_zamowienia_klienta, status_wysylki_id, opis_zakonczenia, oferta_id)VALUES (''<%p_numer%>'',''<%p_data_utworzenia%>'',''<%p_data_zamowienia%>'',''<%p_termin%>'', <%p_pracownik_id%>, <%p_klient_id%>, <%p_status_id%>, ''<%p_uwagi%>'', <%p_sposob_zamowienia_id%>, <%p_tracking%>, <%p_adres_wysylki_id%>, ''<%p_numer_zamowienia_klienta%>'', <%p_status_wysylki_id%>,''<%p_opis_zakonczenia%>'', <%p_oferta_id%>)RETURNING id;')
WHERE id = 2;

UPDATE queries_oem.cls_zlecenie 
SET query = 'UPDATE oem.zlecenie
	SET 
		numer=''<%p_numer%>'', 	
		data_utworzenia=''<%p_data_utworzenia%>'', 		
		data_zamowienia=''<%p_data_zamowienia%>'',
		termin=''<%p_termin%>'',
		pracownik_id=<%p_pracownik_id%>, 
		klient_id=<%p_klient_id%>,
		status_id=<%p_status_id%>,
		uwagi = ''<%p_uwagi%>'',	
		sposob_zamowienia_id=<%p_sposob_zamowienia_id%>, 
		tracking=<%p_tracking%>,
		adres_wysylki_id = <%p_adres_wysylki_id%>,
		numer_zamowienia_klienta = ''<%p_numer_zamowienia_klienta%>'',
		status_wysylki_id = <%p_status_wysylki_id%>,
		opis_zakonczenia = ''<%p_opis_zakonczenia%>''
		oferta_id = <%p_oferta_id%>,		
	WHERE id=<%p_id_zlecenie%>;'
WHERE id = 3;

CREATE TABLE queries_oem.cls_sposob_wysylki_oferty
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_sposob_wysylki_ofert_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
INSERT INTO queries_oem.cls_sposob_wysylki_oferty (query) VALUES ('SELECT * FROM oem.sposob_wysylki_oferty where id=<%p_sposob_wysylki_oferty_id%>;');
INSERT INTO queries_oem.cls_sposob_wysylki_oferty (query) VALUES ('INSERT INTO oem.sposob_wysylki_oferty (nazwa)
	VALUES (''<%p_nazwa%>'')
	RETURNING id;');

INSERT INTO queries_oem.cls_sposob_wysylki_oferty (query) VALUES ('UPDATE oem.sposob_wysylki_oferty
	SET 
		nazwa=''<%p_nazwa%>''		
	WHERE id=<%p_sposob_wysylki_oferty_id%>;');

INSERT INTO queries_oem.cls_sposob_wysylki_oferty (query) VALUES ('DELETE FROM oem.sposob_wysylki_oferty WHERE id=<%p_id_sposob_wysylki_oferty%>');
INSERT INTO queries_oem.cls_sposob_wysylki_oferty (query) VALUES ('SELECT * FROM oem.sposob_wysylki_oferty;');


CREATE TABLE queries_oem.cls_email
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_email_pk PRIMARY KEY (id)
);

INSERT INTO queries_oem.cls_email (query) VALUES ('SELECT * FROM oem.email WHERE id=<%p_id_email%>;');
INSERT INTO queries_oem.cls_email (query) VALUES ('INSERT INTO oem.email (klient_id,email)
	VALUES (<%p_klient_id%>,''<%p_email%>'')
	RETURNING id;');

INSERT INTO queries_oem.cls_email (query) VALUES ('UPDATE oem.email
	SET 
		klient_id=<%p_klient_id%>, 	
		email=''<%p_email%>''				
	WHERE id=<%p_id_email%>;');

INSERT INTO queries_oem.cls_email (query) VALUES ('DELETE FROM oem.email WHERE id=<%p_id_email%>;');
INSERT INTO queries_oem.cls_email (query) VALUES ('SELECT * FROM oem.email WHERE klient_id=<%p_klient_id%>;');



CREATE TABLE queries_oem.cls_oferta
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_oferta_pk PRIMARY KEY (id)
);

INSERT INTO queries_oem.cls_oferta (query) VALUES ('SELECT * FROM oem.oferta where id=<%p_id_oferty%>;');
INSERT INTO queries_oem.cls_oferta (query) VALUES ('INSERT INTO oem.oferta(data_oferty, data_waznosci, pracownik_id, klient_id, status_oferty_id, potwierdzenie_id ,sposob_wysylki_oferty_id, uwagi, numer, email_id,  opis_zakonczenia, zlecenie_id, czas_realizacji)
VALUES (''<%p_data_oferty%>'',''<%p_data_waznosci%>'', <%p_pracownik_id%>, <%p_klient_id%>, <%p_status_oferty_id%>,<%p_potwierdzenie_id%>, <%p_sposob_wysylki_oferty_id%>,  ''<%p_uwagi%>'',''<%p_numer%>'', <%p_email_id%>,''<%p_opis_zakonczenia%>'', <%p_zlecenie_id%>, ''<%p_czas_realizacji%>'')
RETURNING id;');
INSERT INTO queries_oem.cls_oferta (query) VALUES ('UPDATE oem.oferta 
SET 
data_oferty=''<%p_data_oferty%>'', 
data_waznosci=''<%p_data_waznosci%>'', 
pracownik_id=<%p_pracownik_id%>, 
klient_id=<%p_klient_id%>, 
status_oferty_id=<%p_status_oferty_id%>, 
potwierdzenie_id=<%p_potwierdzenie_id%>, 
sposob_wysylki_oferty_id = <%p_sposob_wysylki_oferty_id%>, 
uwagi=''<%p_uwagi%>'', 
numer=''<%p_numer%>'', 
email_id=<%p_email_id%>,
opis_zakonczenia=''<%p_opis_zakonczenia%>'', 
zlecenie_id=<%p_zlecenie_id%>,
czas_realizacji=''<%p_czas_realizacji%>'' 
WHERE id = <%p_id_oferty%>;');
INSERT INTO queries_oem.cls_oferta (query) VALUES ('DELETE FROM oem.oferta WHERE id=<%p_id_oferty%>;');
INSERT INTO queries_oem.cls_oferta (query) VALUES ('SELECT id FROM oem.pozycje_oferty WHERE oferta_id=<%p_id_oferty%> ORDER BY id;');




CREATE TABLE queries_oem.cls_oferta_adres_email
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_oferta_adres_email_id_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

INSERT INTO queries_oem.cls_oferta_adres_email (query) VALUES ('SELECT * FROM oem.oferta_adres_email WHERE id=<%p_id_oferta_adres_emaill%>;');
INSERT INTO queries_oem.cls_oferta_adres_email (query) VALUES ('INSERT INTO oem.oferta_adres_email (oferta_id,email_id)
	VALUES (<%p_oferta_id%>,<%p_email_id%>)
	RETURNING id;');
INSERT INTO queries_oem.cls_oferta_adres_email (query) VALUES ('UPDATE oem.oferta_adres_email
	SET 
		oferta_id=<%p_oferta_id%>, 	
		email_id=<%p_email_id%>				
	WHERE id=<%p_id_oferta_adres_email%>;');
INSERT INTO queries_oem.cls_oferta_adres_email (query) VALUES ('DELETE FROM oem.oferta_adres_email WHERE id=<%p_id_oferta_adres_email%>');
INSERT INTO queries_oem.cls_oferta_adres_email (query) VALUES ('SELECT * FROM oem.oferta_adres_email WHERE oferta_id=<%p_oferta_id%>;');



CREATE TABLE queries_oem.cls_wysylka_oferty
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_wysylka_oferty_pk PRIMARY KEY (id)
);

INSERT INTO queries_oem.cls_wysylka_oferty (query) VALUES ('SELECT * FROM oem.wysylka_oferty WHERE id = <%%p_id_wysylka_oferty%>;');
INSERT INTO queries_oem.cls_wysylka_oferty (query) VALUES ('INSERT INTO oem.wysylka_oferty (oferta_id, data_wysylki, pracownik_id)
	VALUES (<%p_oferta_id%>, ''<%p_data_wysylki%>'', <%p_pracownik_id%>) RETURNING id;');
INSERT INTO queries_oem.cls_wysylka_oferty (query) VALUES ('UPDATE oem.wysylka_oferty
	
SET 
		
	oferta_id=<%p_oferta_id%>,
	data_wysylki=''<%p_data_wysylki%>'',
	pracownik_id=<%p_pracownik_id%>

	WHERE id=<%p_id_wysylka_oferty%>;');

INSERT INTO queries_oem.cls_wysylka_oferty (query) VALUES ('DELETE FROM oem.wysylka_oferty WHERE id=<%p_id_wysylka_oferty%>');

INSERT INTO queries_oem.cls_wysylka_oferty (query) VALUES ('SELECT * FROM oem.wysylka_oferty WHERE oferta_id=<%p_oferta_id%>;');



CREATE TABLE queries_oem.cls_status_oferty
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_status_oferty_pk PRIMARY KEY (id)
);

INSERT INTO queries_oem.cls_status_oferty (query) VALUES ('SELECT * FROM oem.status_oferty where id=<%p_id_status_oferty%>;');
INSERT INTO queries_oem.cls_status_oferty (query) VALUES ('INSERT INTO oem.status_oferty(nazwa)
	VALUES (''<%p_nazwa%>'')
	RETURNING id;');
INSERT INTO queries_oem.cls_status_oferty (query) VALUES ('UPDATE oem.status_oferty
	SET 
		nazwa=''<%p_nazwa%>''		
	WHERE id=<%p_id_status_oferty%>;');
INSERT INTO queries_oem.cls_status_oferty (query) VALUES ('DELETE FROM oem.status_oferty WHERE id=<%p_id_status_oferty%>');
INSERT INTO queries_oem.cls_status_oferty (query) VALUES ('SELECT * FROM oem.status_oferty;');


CREATE TABLE queries_oem.cls_pozycje_oferty
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_pozycje_oferty_id_pk PRIMARY KEY (id)
);	
INSERT INTO queries_oem.cls_pozycje_oferty (query) VALUES ('SELECT * FROM oem.pozycje_oferty where id=<%p_id_pozycje_oferty%>;');
INSERT INTO queries_oem.cls_pozycje_oferty (query) VALUES ('INSERT INTO oem.pozycje_oferty(oferta_id, numer_pozycji, gatunek_id, wymiar, tol_dolna, tol_gorna, wydluzenie, wytrzymalosc_min, ilosc, jednostka_miary_id, uwagi, cena, rabat, wytrzymalosc_max, pakowanie_id, cena_opakowania, waluta_id, powierzony_material, magazyn_id, swiadectwo)
	VALUES (<%p_oferta_id%>,<%p_numer_pozycji%>, <%p_gatunek_id%>, ''<%p_wymiar%>'', ''<%p_tol_dolna%>'', ''<%p_tol_gorna%>'', ''<%p_wydluzenie%>'',''<%p_wytrzymalosc_min%>'', ''<%p_ilosc%>'', <%p_jednostka_miary_id%>, ''<%p_uwagi%>'', <%p_cena%>, <%p_rabat%>, ''<%p_wytrzymalosc_max%>'', <%p_pakowanie_id%>, <%p_cena_opakowania%>, <%p_waluta_id%>, <%p_powierzony_material%>, <%p_magazyn_id%>, <%p_swiadectwo%>)
	RETURNING id;');
INSERT INTO queries_oem.cls_pozycje_oferty (query) VALUES ('UPDATE oem.pozycje_oferty
	SET 
		oferta_id=<%p_oferta_id%>, 	
		numer_pozycji=<%p_numer_pozycji%>, 	
		gatunek_id=<%p_gatunek_id%>,
		wymiar=''<%p_wymiar%>'',
		tol_dolna=''<%p_tol_dolna%>'',
		tol_gorna=''<%p_tol_gorna%>'', 	
		wydluzenie=''<%p_wydluzenie%>'',
		wytrzymalosc_min=''<%p_wytrzymalosc_min%>'',
		ilosc=''<%p_ilosc%>'', 	
		jednostka_miary_id=<%p_jednostka_miary_id%>,
		uwagi=''<%p_uwagi%>'',
		cena=<%p_cena%>,
		rabat=<%p_rabat%>,
		wytrzymalosc_max=''<%p_wytrzymalosc_max%>'',
		pakowanie_id=<%p_pakowanie_id%>,
		cena_opakowania=<%p_cena_opakowania%>,
		waluta_id=<%p_waluta_id%>,
		powierzony_material = <%p_powierzony_material%>,
		magazyn_id = <%p_magazyn_id%>,
		swiadectwo = <%p_swiadectwo%> 
WHERE id=<%p_id_pozycje_oferty%>;');
INSERT INTO queries_oem.cls_pozycje_oferty (query) VALUES ('DELETE FROM oem.pozycje_oferty WHERE id=<%p_id_pozycje_oferty%>');



-----------------------------SCHEMAT queries_application_oem --------------------------------------------
CREATE TABLE queries_application_oem.cls_oferta
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cls_oferta_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);


INSERT INTO queries_application_oem.cls_oferta (query)
	VALUES ('SELECT oferta.id, oferta.numer, klient.nazwa_skrocona, status_oferty.nazwa, oferta.data_oferty FROM oem.oferta, oem.klient, oem.status_oferty WHERE oferta.klient_id = klient.id AND oferta.status_oferty_id = status_oferty.id AND <%p_zapytanie%> <%p_zapytanie1%> GROUP BY oferta.id, klient.nazwa_skrocona, status_oferty.nazwa ORDER BY oferta.numer DESC;');


-- Table: queries_application_oem.cls_szukanie_oferty

-- DROP TABLE queries_application_oem.cls_szukanie_oferty;

CREATE TABLE queries_application_oem.cls_szukanie_oferty
(
  id serial NOT NULL,
  query character varying(5000),
  CONSTRAINT cla_szukanie_oferty_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE queries_application_oem.cls_szukanie_oferty
  OWNER TO postgres;


INSERT INTO queries_application_oem.cls_szukanie_oferty (query)
	VALUES ('SELECT id, pracownik_id, oferta_id, informacje FROM oem.potwierdzenie_oferty WHERE oferta_id = <%p_oferta_id%>;');


--------------------------------------------------

-- Function: oem.pobierz_numer_oferty()

-- DROP FUNCTION oem.pobierz_numer_oferty();

CREATE OR REPLACE FUNCTION oem.pobierz_numer_oferty()
  RETURNS character varying AS
$BODY$
DECLARE
   numer_oferty character varying(10);
   MIESIAC integer;
   ROK integer;
   kolejny_numer integer;
BEGIN

	SELECT wartosc INTO MIESIAC FROM oem.numer_oferty_ustawienia WHERE nazwa='MIESIAC';
	SELECT wartosc INTO ROK FROM oem.numer_oferty_ustawienia WHERE nazwa='ROK';
	SELECT wartosc INTO kolejny_numer FROM oem.numer_oferty_ustawienia WHERE nazwa='LICZNIK';

	IF MIESIAC = EXTRACT(MONTH FROM NOW()) AND ROK = EXTRACT(YEAR FROM NOW()) THEN
		numer_oferty=to_char(ROK,'FM0000') || to_char(MIESIAC,'FM00') || to_char(kolejny_numer,'FM000');
		UPDATE oem.numer_oferty_ustawienia SET wartosc=wartosc+1 WHERE nazwa='LICZNIK';
	END IF;

	IF MIESIAC <> EXTRACT(MONTH FROM NOW()) AND ROK = EXTRACT(YEAR FROM NOW()) THEN
		numer_oferty=to_char(ROK,'FM0000') || to_char(EXTRACT(MONTH FROM NOW()),'FM00') || '001';
		UPDATE oem.numer_oferty_ustawienia SET wartosc=2 WHERE nazwa='LICZNIK';
		UPDATE oem.numer_oferty_ustawienia SET wartosc=EXTRACT(MONTH FROM NOW()) WHERE nazwa='MIESIAC';
	END IF;

	IF MIESIAC <> EXTRACT(MONTH FROM NOW()) AND ROK <> EXTRACT(YEAR FROM NOW()) THEN
		numer_oferty=EXTRACT(YEAR FROM NOW()) || to_char(EXTRACT(MONTH FROM NOW()),'FM00') || '001';
		UPDATE oem.numer_oferty_ustawienia SET wartosc=2 WHERE nazwa='LICZNIK';
		UPDATE oem.numer_oferty_ustawienia SET wartosc=EXTRACT(MONTH FROM NOW()) WHERE nazwa='MIESIAC';
		UPDATE oem.numer_oferty_ustawienia SET wartosc=EXTRACT(YEAR FROM NOW()) WHERE nazwa='ROK';
	END IF;
	RETURN numer_oferty;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION oem.pobierz_numer_oferty()
  OWNER TO postgres;

INSERT INTO oem.numer_oferty_ustawienia (nazwa, wartosc) VALUES ('MIESIAC', 2);
INSERT INTO oem.numer_oferty_ustawienia (nazwa, wartosc) VALUES ('ROK', 2014);
INSERT INTO oem.numer_oferty_ustawienia (nazwa, wartosc) VALUES ('LICZNIK', 0);

INSERT INTO oem.sposob_zamowienia(nazwa) VALUES('oferta');
